﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using EnglishRemix.Services.Model;
using EnglishRemix.Services.Utility;
using System.Data;
using EnglishRemix.Services.Constants;
using EnglishRemix.Services.Model.Responses;
using EnglishRemix.Services.Model.Requests;
using EnglishRemix.Services.Model.Exceptions;

namespace EnglishRemix.Services.Facade
{
    public class SystemFacade
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        readonly string spAddUserAchievement = "spAddUserAchievement";


        public SystemFacade()
        {
            log.Debug("SystemFacade initialized");
        }

        public void Log(String UserId, String LogType, String LogName, String Content, int Result)
        {
            log.Debug("Log");
            Int16 ActionId = 0;
            Int16 ActivityId = 0;

            if (LogType.Equals("ACTION"))
                ActionId = Convert.ToInt16(LogName);
            else if (LogType.Equals("ACTIVITY"))
                ActivityId = Convert.ToInt16(LogName);

            List<SqlParameter> sqlParam = new List<SqlParameter>();
            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, Convert.ToInt16(UserId));
            DBUtility.AddSqlParam(sqlParam, "@LogType", SqlDbType.BigInt, Convert.ToInt16(LogType));
            DBUtility.AddSqlParam(sqlParam, "@Action", SqlDbType.BigInt, ActionId);
            DBUtility.AddSqlParam(sqlParam, "@Activity", SqlDbType.BigInt, ActivityId);
            DBUtility.AddSqlParam(sqlParam, "@Content", SqlDbType.NVarChar, Content);
            DBUtility.AddSqlParam(sqlParam, "@Result", SqlDbType.SmallInt, Result);
            DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
            DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now.ToString(ConfigurationManager.AppSettings["DateFormat"]));
            DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");

            log.Debug("UserId:" + UserId + ", LogType:" + LogType + ", LogName:" + LogName + ", Result:" + Result + ", Content:" + Content);
            
            string sqlStr = "INSERT INTO SystemLog (UserId, LogType, Activity, Action, Content, Result,  CreatedOn, LastModifiedDate, LastModifiedBy) VALUES " +
                "(@UserId, @LogType, @Activity, @Action, @Content, @Result, @CreatedOn, @LastModifiedDate, @LastModifiedBy);";
            //TODO: Throw exception???
            if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) == 1)
            {
                log.Debug("Insert Log Done.");
                //return true;
            }
            else
            {
                log.Debug("Insert Log Fail.");
                //return false;
            }
        }

        public PurchaseProductResponse GetPurchaseProduct(PurchaseProductRequest purchaseRequest)
        {
            Model.Responses.PurchaseProductResponse resp = new Model.Responses.PurchaseProductResponse();
            List<PurchaseProductInfo> purchaseProductList = new List<PurchaseProductInfo>();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = @"select * from AvaliableProducts";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    PurchaseProductInfo info = new PurchaseProductInfo();
                    info.ProductName = dr["Name"].ToString();
                    info.Price = Convert.ToDecimal(dr["Price"]);
                    info.Coin = Convert.ToInt32(dr["Coin"]);
                    info.ProductDescription = dr["Description"].ToString();
                    info.Title = dr["Title"].ToString();
                    info.Identification = dr["Identification"].ToString();
                    purchaseProductList.Add(info);
                }
            }
            resp.PurchaseProductList = purchaseProductList;
            return resp; 
        }
        
        public void AddUserAction(string uId, string methodName, string s1, string s2, string s3)
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            int? actionTypeId = null;
            string parameter = null;

            string sqlStr = @"select Id from ActionType where Name = @Name";
            DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, methodName);
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);

            if (DBUtility.hasResult(ds))
            {//ActionType ID exist
                actionTypeId = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
            }
            else
            {//ActionType doesn't exist and insert the new ActionType ID
                string str = @"insert into ActionType (Name, CreatedOn, LastModifiedDate, LastModifiedBy) Values " +
                                "(@Name, @CreatedOn, @LastModifiedDate, @LastModifiedBy);";
                DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now);
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
                DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");
                if (DBUtility.ExecuteNonQuery(str, sqlParam) == 1)
                {
                    DataSet ds1 = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                    actionTypeId = Convert.ToInt32(ds1.Tables[0].Rows[0]["Id"]);
                }
                else
                {
                    throw new ServiceException("Insert ActionType failed. UserId: " + uId);
                }
            }
            //Construct parameter string in UserAction table
            switch (methodName)
            {                
                case "register":
                    sqlParam.Clear();
                    DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, s1);
                    DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, s2);
                    sqlStr = @"select Id from UserInfo where Name = @Name and Password = @Password";                    
                    DataSet RegDS = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                    if(DBUtility.hasResult(RegDS))
                    {                        
                        uId = RegDS.Tables[0].Rows[0]["Id"].ToString();
                    }
                    else
                    {
                        throw new ServiceException("Insert Action for Register failed.");
                    }
                    //parameter string: "UserName"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                                        
                    break;
                case "login":
                    sqlParam.Clear();
                    DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, s1);
                    DBUtility.AddSqlParam(sqlParam, "@Password", SqlDbType.NVarChar, s2);
                    sqlStr = @"select Id from UserInfo where Name = @Name and Password = @Password";                    
                    DataSet loginDS = DBUtility.ExecuteDataset(sqlStr, sqlParam);
                    if(DBUtility.hasResult(loginDS))
                    {                        
                        uId = loginDS.Tables[0].Rows[0]["Id"].ToString();
                    }
                    else
                    {
                        throw new ServiceException("Insert Action for Login failed.");
                    }
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break; 
                case "getuserachievement":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break; 
                case "getallachievements":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break;
                case "getchapter":
                    //parameter string: "UserId, chaperNumber"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";                    
                    break;
                case "updateactivitystatusbyuser":
                    //parameter string: "UserId, ActivityId"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";                    
                    break;
                case "getuseractivitystatsus":
                    //parameter string: "UserId, chaperNumber"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";                    
                    break;
                case "getuser":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break;
                case "updateuser":
                    //parameter string: "UserId, Coin, level, ActivitionStatus"
                    parameter = uId + "," + s1 + "," + s2 + "," + s3;                    
                    break;
                case "changepassword":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break;
                case "purchaseactivity":
                    //parameter string: "UserId, ActivityId"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";                    
                    break;
                case "adduserfavorite":
                    //parameter string: "UserId, ChapterNumber, PageNubmer"
                    parameter = uId + "," + s1 + "," + s2 + "," + "0";                    
                    break;
                case "getuserfavorite":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                     
                    break;
                case "deleteuserfavorite":
                    //parameter string: "UserId, ChapterNumber, PageNubmer"
                    parameter = uId + "," + s1 + "," + s2 + "," + "0";                     
                    break;                 
                case "uploadvideo":
                    //parameter string: "UserId, Aid, ChapterNumber, SelfScore "
                    parameter = uId + "," + s1 + "," + s2 + "," + s3;                    
                    break;
                case "getuploadedvideolist":
                    //parameter string: "UserId, ActivityId, SentenceNumber"
                    parameter = uId + "," + s1 + "," + s2 + "," + "0";                     
                    break;
                case "getuploadedvideobyid":
                    //parameter string: "UserId, VideoId"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";
                    break;
                case "getchapterlist":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break;
                case "postcomment":
                    //parameter string: "UserId, CreatorId, ToCommentId"
                    parameter = uId + "," + s1 + "," + s2 + "," + "0";                    
                    break;
                case "getcommentbyuploadedvideoid":
                    //parameter string: "UserId, UploadedVideoId"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";                    
                    break;
                case "rateuploadedvideo":
                    //parameter string: "UserId, CreatorId, VideoId, Score"
                    parameter = uId + "," + s1 + ","+s2 + "," + s3;                    
                    break;
                case "getrankingvideo":
                    //parameter string: "UserId, Score, Ranking"
                    parameter = uId + "," + s1 + "," + s2 + "," + "0";                    
                    break;
                case "purchasecoin":
                    //parameter string: "UserId, PurchasedCoin"
                    parameter = uId + "," + s1 + "," + "0" + "," + "0";                    
                    break;
                case "getpurchaseproduct":
                    //parameter string: "UserId"
                    parameter = uId + "," + "0" + "," + "0" + "," + "0";                    
                    break;
            }
            AddUserActionTable(uId, actionTypeId, parameter);
        }
        private void AddUserActionTable(string uId, int? action, string parameter)
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = @"insert into UserAction (UserId, Action, Parameter, FulfilledDateTime, CreatedOn, LastModifiedDate, LastModifiedBy) Values " +
                              "(@UserId, @Action, @Parameter, @FulfilledDateTime, @CreatedOn, @LastModifiedDate, @LastModifiedBy);";

            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, Convert.ToInt32(uId));
            DBUtility.AddSqlParam(sqlParam, "@Action", SqlDbType.BigInt, action);
            DBUtility.AddSqlParam(sqlParam, "@Parameter", SqlDbType.NVarChar, parameter);
            DBUtility.AddSqlParam(sqlParam, "@FulfilledDateTime", SqlDbType.DateTime, DateTime.Now);
            DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now);
            DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
            DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");

            if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) == 1)
            {
                log.Debug("Insert done");
            }
            else
            {
                log.Debug("Insert fail");
                throw new ServiceException("User Action Insert failed. UserId: " + uId + "; ActionId: " + action);
            }
        }

        /// <summary>
        /// Debugging method
        /// </summary>
        /// <param name="actionTypeRequest"></param>
        /// <returns></returns>
        public ActionTypeResponse GetActionType(ActionTypeRequest actionTypeRequest)
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();            
            ActionTypeResponse resp = new ActionTypeResponse();
            List<ActionTypeInfo> actionTypeList = new List<ActionTypeInfo>();
            string sqlStr = @"select Id, Name, CreatedOn from ActionType";
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    ActionTypeInfo actionType = new ActionTypeInfo();
                    actionType.Id = Convert.ToInt32(dr["Id"]);
                    actionType.Name = dr["Name"].ToString();
                    actionType.CreatedOn = Convert.ToDateTime(dr["CreatedOn"]);
                    actionTypeList.Add(actionType);
                }
            }
            resp.actionTypeList = actionTypeList;
            return resp;
        }

        public List<UserActionInfo> GetUserActionListByID(int userId)
        {
            List<UserActionInfo> userActionList = new List<UserActionInfo>();
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = @"select 
                                ua.Action as Action, 
                                ua.Parameter as Parameter, 
                                ua.FulfilledDateTime as FulfilledDateTime,
                                at.Name as ActionName,
                                ach.Name as AchievementName,
                                ach.Criteria as AchievementCriteria                                
                                from UserAction ua
                                join ActionType at on at.Id = ua.Action
                                join Achievement ach on ach.ActionType = ua.Action
                                where ua.UserId = @UserId";

            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, userId);
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
           //TODO: Add stored procedure to perform add Achievement
            //DataSet ds1 = DBUtility.ExecuteDataset(sqlStr, CommandType.StoredProcedure, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    UserActionInfo info = new UserActionInfo();
                    info.Action = Convert.ToInt32(dr["Action"]);
                    info.Parameter = dr["Parameter"].ToString();
                    info.FulfilledDateTime = Convert.ToDateTime(dr["FulfilledDateTime"]);
                    userActionList.Add(info);
                }
            }
            return userActionList;
        }

        public void AddAchievement()
        {
            //helper method to preload Achievements into tables
            //Add Achievement table base on user action
            //TODO: Need to check if added already, do nothting
          
           
            //TODO: Define the standard for achievements.
            string achActionName = "register";
            string achCriteria = @"select Id from UserInfo where Id = @UserId and Cast(CreatedON as Date) = Cast(GetDate() as Date)";
            string achDescription = "New Register";
            string achName = "Good starter";

            //@P1: purchase Coins
            string achActionName1 = "purchasecoin";
            string achCriteria1 = @"select * from UserInfo where Id = @UserId and @P1 > 10";
            string achDescription1 = "Purchase Coins";
            string achName1 = "SliverStar";

            //@
            string achActionName2 = "uploadvideo";
            string achCriteria2 = @"select count(*) from UserUploadedVideo where UserInfo = @UserId";
            string achDescription2 = "Upload video";
            string achName2 = "One Gold Star";

            string achActionName3 = "updateuseractivitystatsus";
            string achCriteria3 = @"select status, ChapterNumber, CreatedOn, COUNT(*) as NumOfCount from 
                                    (	select uac.status as status, c.Number as ChapterNumber, Cast(ua.CreatedOn as Date) as CreatedOn
		                                from UserActivity ua
		                                join Activity a on a.Id = ua.Activity
		                                join PageInfo pi on pi.Id = a.PageInfo
		                                join Chapter c on c.Id = pi.Chapter
		                                join UserActivityStatus uac on uac.Id = ua.Status
		                                where ua.UserInfo = @UserId and uac.Status = 'Completed' and c.Number = @P1
	                                ) a
	                                group by status, ChapterNumber, CreatedOn ";
            //Check in same date, 
            //  if NumOfCount > 0; then 1st level achievement given, 
            //  If NumOfCount > 2; then next level achievment 
            //If not same date, 
            //  If numOfCount > 0; then one level
            //  If NumOfCount > 2; then next level achievment 
            //
            string achDescription3 = "Complete Chapter 1";
            string achName3 = "One Gold Star";

            //Stored procedure name in Database
            string spName = "spAddAchievement";
            string resourceUrl = @"D:/SVN/";
          
            SpAddAchievement(achActionName, achCriteria, achDescription, achName, spName, resourceUrl);
            SpAddAchievement(achActionName1, achCriteria1, achDescription1, achName1, spName, resourceUrl);
            SpAddAchievement(achActionName2, achCriteria2, achDescription2, achName2, spName, resourceUrl);
            SpAddAchievement(achActionName3, achCriteria3, achDescription3, achName3, spName, resourceUrl);
        }
        /// <summary>
        /// Call Stored procedure spAddAchievement to add new record 
        /// preloading the achievement records
        /// </summary>
        /// <param name="achActionName"></param>
        /// <param name="achCriteria"></param>
        /// <param name="achDescription"></param>
        /// <param name="achName"></param>
        /// <param name="spName"></param>
        /// <param name="resourceUrl"></param>
        private void SpAddAchievement(string achActionName, string achCriteria, string achDescription, string achName, string spName, string resourceUrl)
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();           
            DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, achName);
            DBUtility.AddSqlParam(sqlParam, "@Description", SqlDbType.NVarChar, achDescription);
            DBUtility.AddSqlParam(sqlParam, "@ActionName", SqlDbType.NVarChar, achActionName);
            DBUtility.AddSqlParam(sqlParam, "@Criteria", SqlDbType.NVarChar, achCriteria);
            DBUtility.AddSqlParam(sqlParam, "@ResourceUrl", SqlDbType.NVarChar, resourceUrl);
            if (DBUtility.ExecuteNonQuery(spName, CommandType.StoredProcedure, sqlParam) != 1)
            {
                throw new ServiceException("Insert Achievement failed. ActionName: " + achActionName);
            }
        }

        public AddAchievementResponse AddUserAchievement(AddAchievementRequest addAchievementRequest)
        {
            AddAchievementResponse resp = new AddAchievementResponse();
            int userId = 0;
            int achievementId = 0;
            string parameter = null;
            string criteria = null;
            int userActionId = 0;
            string sqlStr = null;
            //string spName = "spAddUserAchievement";

            List<SqlParameter> sqlParam = new List<SqlParameter>();

            if(addAchievementRequest.ActionName != null && addAchievementRequest.ActionName.Length > 0)
            {
                sqlStr = @"select ua.Id as UserActionId, ua.UserId as UserId, ach.Id as AchievementId, ua.Parameter as Parameter, ach.Criteria as Criteria
                                from UserAction ua
                                join Achievement ach on ach.ActionType = ua.Action
                                join ActionType at on at.Id = ach.ActionType 
                                where ua.UserId = @UserId and at.Name = @ActionName";
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, addAchievementRequest.UserId);
                DBUtility.AddSqlParam(sqlParam, "@ActionName", SqlDbType.NVarChar, addAchievementRequest.ActionName);            
            }
            else
            {
                sqlStr = @"select ua.Id as UserActionId, ua.UserId as UserId, ach.Id as AchievementId, ua.Parameter as Parameter, ach.Criteria as Criteria
                                from UserAction ua
                                join Achievement ach on ach.ActionType = ua.Action                                
                                where ua.UserId = @UserId";
                DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, addAchievementRequest.UserId);
            }                   
            //Retrieve the user achievement and related information
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                {
                    userId = Convert.ToInt32(dr["UserId"]);
                    achievementId = Convert.ToInt32(dr["AchievementId"]);                    
                    userActionId = Convert.ToInt32(dr["UserActionId"]);
                    parameter = dr["Parameter"].ToString();
                    criteria = dr["Criteria"].ToString();
                
                    int[] parameterArray = GetParameterList(parameter);
                    if (!isUserAchievementExist(userId, userActionId))
                    {
                        sqlParam.Clear();
                        DBUtility.AddSqlParam(sqlParam, "@P1", SqlDbType.BigInt, parameterArray[1]);
                        DBUtility.AddSqlParam(sqlParam, "@P2", SqlDbType.BigInt, parameterArray[2]);
                        DBUtility.AddSqlParam(sqlParam, "@P3", SqlDbType.BigInt, parameterArray[3]);
                                       
                        DataSet criteriaSet = DBUtility.ExecuteDataset(criteria, sqlParam);
                        if (DBUtility.hasResult(criteriaSet))
                        {//Criteria is good to insert the userAchievement
                            sqlParam.Clear();
                            DBUtility.AddSqlParam(sqlParam, "@UserActionId", SqlDbType.BigInt, userActionId);
                            DBUtility.AddSqlParam(sqlParam, "@Achievement", SqlDbType.BigInt, achievementId);
                            DBUtility.AddSqlParam(sqlParam, "@CreatedOn", SqlDbType.DateTime, DateTime.Now);
                            DBUtility.AddSqlParam(sqlParam, "@LastModifiedDate", SqlDbType.DateTime, DateTime.Now);
                            DBUtility.AddSqlParam(sqlParam, "@LastModifiedBy", SqlDbType.NVarChar, "System");
                            sqlStr = @"insert into UserAchievement (UserActionId, Achievement, CreatedOn, LastModifiedDate, LastModifiedBy) 
	                                           values( @UserActionId, @Achievement, @CreatedOn, @LastModifiedDate, @LastModifiedBy)";                          
                            if (DBUtility.ExecuteNonQuery(sqlStr, sqlParam) != 1)
                            {
                                throw new ServiceException("Insert User Achievement failed. UserID: " + addAchievementRequest.UserId + "Achievement: " + achievementId);
                            }
                            resp.Message = "User Achievement is inserted.";
                            resp.Result = true;
                        }
                    }//UserAchievement record exist; do nothing;                   
                }//For each query result to check the UserAchievemet and Achievement                 
            }
            //Now: If not Achievement exist, do nothing 
            //TODO: add new achievement record and add criteria string
            //TODO: And Insert the UserAchievement table    
            return resp;
        }

        private bool isUserAchievementExist(int userId, int userActionId)
        {
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = @"select count(*) from UserAchievement uach
                                join UserAction ua on ua.Id = uach.UserActionId             
                                where ua.UserId = @UserId and uach.UserActionId = @UserActionId";
            DBUtility.AddSqlParam(sqlParam, "@UserId", SqlDbType.BigInt, userId);
            DBUtility.AddSqlParam(sqlParam, "@UserActionId", SqlDbType.BigInt, userActionId);
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if (DBUtility.hasResult(ds))
                return true;
            else
                return false;
        }

        private int[] GetParameterList(string parameter)
        {
            string[] separators = { ",", ".", "!", "?", ";", ":", " ", "|" };
            string[] words = parameter.Split(separators, StringSplitOptions.RemoveEmptyEntries);            
            List<int> parameterList = new List<int>();
            foreach (var word in words)
            {
                int p = Convert.ToInt32(word);
                parameterList.Add(p);
            }

            return parameterList.ToArray();
        }      
        
        private int GetActionTypeId(string actionName)
        {
            int actionTypeId = 0;
            List<SqlParameter> sqlParam = new List<SqlParameter>();
            string sqlStr = @"select Id from ActionType where Name = @Name";
            DBUtility.AddSqlParam(sqlParam, "@Name", SqlDbType.NVarChar, actionName);
            DataSet ds = DBUtility.ExecuteDataset(sqlStr, sqlParam);
            if(DBUtility.hasResult(ds))
            {
                actionTypeId = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
            }

            return actionTypeId;
        }      
    }
}
